using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;

namespace Lzfy_Refund_Service.Repositories
{
    public class RefundDictRepository : IRefundDictRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;

        public RefundDictRepository(IDbConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public async Task<RefundDict?> GetByIdAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id as Id,
                input_date as InputDate,
                dict_id as DictId,
                dict_name as DictName,
                item_name as ItemName,
                item_value as ItemValue
                FROM lzfy_refund_dict WHERE id = @Id";
            return await connection.QueryFirstOrDefaultAsync<RefundDict>(sql, new { Id = id });
        }

        public async Task<IEnumerable<RefundDict>> GetAllAsync()
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id as Id,
                input_date as InputDate,
                dict_id as DictId,
                dict_name as DictName,
                item_name as ItemName,
                item_value as ItemValue
                FROM lzfy_refund_dict ORDER BY dict_id, item_value";
            return await connection.QueryAsync<RefundDict>(sql);
        }

        public async Task<IEnumerable<RefundDict>> GetByDictIdAsync(int dictId)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id as Id,
                input_date as InputDate,
                dict_id as DictId,
                dict_name as DictName,
                item_name as ItemName,
                item_value as ItemValue
                FROM lzfy_refund_dict WHERE dict_id = @DictId ORDER BY item_value";
            return await connection.QueryAsync<RefundDict>(sql, new { DictId = dictId });
        }

        public async Task<RefundDict?> GetByDictIdAndItemValueAsync(int dictId, string itemValue)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id as Id,
                input_date as InputDate,
                dict_id as DictId,
                dict_name as DictName,
                item_name as ItemName,
                item_value as ItemValue
                FROM lzfy_refund_dict WHERE dict_id = @DictId AND item_value = @ItemValue";
            return await connection.QueryFirstOrDefaultAsync<RefundDict>(sql, new { DictId = dictId, ItemValue = itemValue });
        }

        public async Task<int> CreateAsync(RefundDict refundDict)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                INSERT INTO lzfy_refund_dict (dict_id, dict_name, item_name, item_value)
                VALUES (@DictId, @DictName, @ItemName, @ItemValue);
                SELECT CAST(SCOPE_IDENTITY() as int);";
            
            return await connection.QuerySingleAsync<int>(sql, refundDict);
        }

        public async Task<bool> UpdateAsync(RefundDict refundDict)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                UPDATE lzfy_refund_dict SET 
                    dict_id = @DictId, 
                    dict_name = @DictName, 
                    item_name = @ItemName, 
                    item_value = @ItemValue
                WHERE id = @Id";
            
            var rowsAffected = await connection.ExecuteAsync(sql, refundDict);
            return rowsAffected > 0;
        }

        public async Task<bool> DeleteAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "DELETE FROM lzfy_refund_dict WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
            return rowsAffected > 0;
        }

        public async Task<IEnumerable<DictCategory>> GetDictCategoriesAsync()
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                dict_id as DictId,
                dict_name as DictName,
                COUNT(*) as ItemCount
                FROM lzfy_refund_dict 
                GROUP BY dict_id, dict_name 
                ORDER BY dict_id";
            return await connection.QueryAsync<DictCategory>(sql);
        }
    }
}